package com.itheima.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface ReportMapper extends BaseMapper {
    @Select("SELECT count(mt.id) as totalCounts,concat(year(now())-1,\"-\",rd.fmonth) as yearAndMonth " +
            "FROM  member_t mt, rpt_date rd " +
            "WHERE  mt.create_date = rd.fdate and  rd.fyear = year(now())-1 " +
            "group by month(mt.create_date)")
    List<Map> totalMemberCounts();

    @Select("SELECT count(st.id) as value,st.name as name " +
            "FROM setmeal_t st,order_t ort " +
            "WHERE ort.setmeal_id = st.id " +
            "group by st.id")
    List<Map> orderSetmealProportion();

    @Select("select count(mt.id) as totalCounts,concat(year(mt.create_Date),\"-\",month(mt.create_Date)) as yearAndMonth \n" +
            "from  member_t mt \n" +
            "where   mt.create_date BETWEEN #{startMonth}  and  #{endMonth}\n" +
            "group by month(mt.create_Date)")
    List<Map> getMemberCountsByMonth(@Param("startMonth") String startMonth, @Param("endMonth") String endMonth);

    @Select("select case when sex = '1' then '男' else '女' end name ,\n" +
            "count(*) value from member_t \n" +
            "group by case when sex ='1' then '男' else '女' end  ")
    List<Map> memberSexProportion();

    @Select("select case when age between 0 and 18 then '0-18岁' when age between 18 and 30 then '18-30岁'\n" +
            "when age between 30 and 45 then '30-45岁' else '45+'\n" +
            "end as name,count(*) as value\n" +
            "from (select  FLOOR(DATEDIFF(DATE_FORMAT(now(),'%Y%m%d'),substring(mt.id_Card,7,8))/365.25) age from member_t mt)\n" +
            "a\n" +
            "group by case when age between 0 and 18 then '0-18岁' when age between 18 and 30 then '18-30岁'\n" +
            "when age between 30 and 45 then '30-45岁' else '45+' end")
    List<Map> memberAgeProportion();

    @Select("SELECT COUNT(1) FROM member_t mt , rpt_date rd WHERE mt.create_date = rd.fdate AND rd.fdate=DATE(NOW())")
    Integer todayNewMember();

    @Select("SELECT COUNT(1) FROM member_t mt , rpt_date rd WHERE mt.create_date = rd.fdate AND rd.fweek=WEEK(NOW())+1")
    Integer thisWeekNewMember();

    @Select("SELECT COUNT(1) FROM member_t ")
    Integer totalMember();

    @Select("SELECT COUNT(1) FROM member_t mt , rpt_date rd WHERE mt.create_date = rd.fdate AND rd.fmonth=MONTH(NOW())")
    Integer thisMonthNewMember();

    @Select("SELECT COUNT(1) FROM order_t ot , rpt_date rd WHERE ot.order_date = rd.fdate AND rd.fdate=DATE(NOW())")
    Integer todayOrderNumber();

    @Select("SELECT COUNT(1) FROM order_t ot , rpt_date rd WHERE ot.order_date = rd.fdate AND rd.fdate=DATE(NOW()) AND STATUS=2")
    Integer todayVisitsNumber();

    @Select("SELECT COUNT(1) FROM order_t ot , rpt_date rd WHERE ot.order_date = rd.fdate AND rd.fweek=WEEK(NOW())")
    Integer thisWeekOrderNumber();

    @Select("SELECT COUNT(1) FROM order_t ot , rpt_date rd WHERE ot.order_date = rd.fdate AND rd.fweek=WEEK(NOW()) AND STATUS=2")
    Integer thisWeekVisitsNumber();

    @Select("SELECT COUNT(1) FROM order_t ot , rpt_date rd WHERE ot.order_date = rd.fdate AND rd.fmonth=MONTH(NOW())")
    Integer thisMonthOrderNumber();

    @Select("SELECT COUNT(1) FROM order_t ot , rpt_date rd WHERE ot.order_date = rd.fdate AND rd.fmonth=MONTH(NOW()) AND STATUS=2")
    Integer thisMonthVisitsNumber();

    @Select("SELECT\n" +
            "\tst.`name` `name`,\n" +
            "\t(SELECT COUNT(1) FROM order_t ot WHERE ot.setmeal_id = st.id) setmeal_count,\n" +
            "\t((SELECT COUNT(1) FROM order_t ot WHERE ot.setmeal_id = st.id) / (SELECT COUNT(1) FROM order_t) * 100) proportion,\n" +
            "\tst.remark remark\n" +
            "FROM\n" +
            "\tsetmeal_t st")
    List<Map<String, String>> hotSetmeal();

}
